package com.financing.controller.report;
import java.io.OutputStream;
import java.util.Arrays;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import com.financing.enums.invest.BidStatus;
import com.financing.model.Data;
import com.financing.model.invest.InvestLog;
import com.financing.model.invest.Trade;
import com.financing.model.invest.Withdraw;
import com.financing.model.user.User;
import com.financing.service.user.UserService;
import com.financing.service.user.WithdrawService;
import com.financing.util.DateUtil;
import com.financing.util.Json;
import com.financing.util.Money;
import com.financing.util.Str;
import com.jfinal.aop.Clear;
import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.SqlPara;

public class ReportController extends Controller {
	
	private static UserService user = new UserService();
	private static WithdrawService withdraw = new WithdrawService();
	
	
	/**
	 * 交易数据统计
	 */
	@Clear
	@SuppressWarnings("unchecked")
	public void trade(){
		
		// 获取参数
		// String month = getPara("month",DateUtil.format("yyyy-MM"));
		String start = DateUtil.adjust(-7,"yyyy-MM-dd")+" 00:00:00";
		String end = DateUtil.format("yyyy-MM-dd")+" 23:59:59";
		Map<String,Object> res = new HashMap<String,Object>();

		// 计算交易金额
		Trade.me.find("select * from trade where create_time >= ? and create_time<=? order by create_time asc",start,end).forEach(t->{
			String dayKey = DateUtil.format(t.getCreateTime(), "yyyy-MM-dd");
			if(res.containsKey(dayKey)) {
				Map<String,Object> m = (Map<String, Object>) res.get(dayKey);
				m.put("min", Money.format(t.getAmount()>=(double)m.get("min")?t.getAmount():(double)m.get("min")));
				m.put("max", Money.format(t.getAmount()>=(double)m.get("max")?t.getAmount():(double)m.get("max")));
				m.put("tm",Money.format((double)m.get("tm")+t.getAmount()));
				m.put("count",(int)m.get("count")+1);
				m.put("bid"+t.getDayLimit(), m.containsKey("bid"+t.getDayLimit())?(double)m.get("bid"+t.getDayLimit())+t.getAmount():t.getAmount());
				res.put(dayKey, m);
			}else{
				Map<String,Object> m = new HashMap<String,Object>();
				m.put("min", t.getAmount());
				m.put("max", t.getAmount());
				m.put("tm", t.getAmount());
				m.put("count", 1);
				m.put("bid"+t.getDayLimit(),t.getAmount());
				res.put(dayKey, m);
			}
		});
		
		// 计算注册 & 实名人数 & 投资人数
		User.me.find("select id,is_real_name_verify,create_time,is_traded from user where create_time >= ? and create_time<=? order by create_time asc",start,end).forEach(u->{
			String dayKey = DateUtil.format(u.getCreateTime(), "yyyy-MM-dd");
			if(res.containsKey(dayKey)) {
				Map<String,Object> m = (Map<String, Object>) res.get(dayKey);
				m.put("reg",m.containsKey("reg")?(int)m.get("reg")+1:1);
				m.put("realName",m.containsKey("realName")?(int)m.get("realName")+u.getIsBindBankCard():u.getIsBindBankCard());
				m.put("trade",m.containsKey("trade")?(int)m.get("trade")+u.getIsTraded():u.getIsTraded());
				res.put(dayKey, m);
			}else{
				Map<String,Object> m = new HashMap<String,Object>();
				m.put("reg",1);
				m.put("realName",u.getIsBindBankCard());
				m.put("trade",u.getIsTraded());
				res.put(dayKey, m);
			}
		});
		
		// 计算充值 & 提现 & 还款金额
		InvestLog.me.find("select type,amount,create_time from invest_log where (type='deposit' or type='withdraw' or type='amount') and status='success' and create_time >= ? and create_time<=? order by create_time asc",start,end).forEach(log->{
			String dayKey = DateUtil.format(log.getCreateTime(), "yyyy-MM-dd");
			String type = log.getType();
			if(res.containsKey(dayKey)) {
				Map<String,Object> m = (Map<String, Object>) res.get(dayKey);
				m.put(type,Money.format(m.containsKey(type)?(double)m.get(type)+log.getAmount():log.getAmount()));
				res.put(dayKey, m);
			}else{
				Map<String,Object> m = new HashMap<String,Object>();
				m.put(type, log.getAmount());
				res.put(dayKey, m);
			}
		});
		renderJson(Json.ok("获取数据成功！",Data.put("res",res)));
	}

	
	/**
	 * 提现数据统计报表
	 */
	@Clear
	public void withdraw(){
		
		// 获取参数
		// String month = getPara("month",DateUtil.format("yyyy-MM"));
		int page = getParaToInt("page",1);
		int size = getParaToInt("size",10);
		Map<String,String> params = Data.flat(getParaMap());
		Page<Withdraw> p = withdraw.list(page,size,params);
		for(Withdraw w : p.getList()) {
			w.put("user", User.me.findById(w.getUserId()));
		}
		renderJson(Json.ok("获取数据成功！"));
	}	
	
	/**
	 * 渠道统计
	 */
	@Clear
	public void channel() throws Exception {

		String mode = getPara("mode","search");
		
		// 搜索渠道统计
		if(Str.eq("search", mode)) {
			// 初始化参数
			Map<String,String> params = Data.flat(getParaMap());
			List<User> list = user.list(1, 999, params).getList();

			// 初始化变量
			int regNum = 0;
			int realNameNum = 0;
			int tradeTotal = 0;
			Set<Integer> tradeNum = new HashSet<Integer>();
			Map<Integer,Integer> tradelMap = new HashMap<Integer,Integer>();
			Map<String,Integer> popChannelMap = new HashMap<String,Integer>();
			Map<String,Integer> downloadChannelMap = new HashMap<String,Integer>();

			// 统计推广/下载数据
			for(User u : list) { 
				regNum++;
				realNameNum += u.getIsBindBankCard();
				popChannelMap.put(u.getPopChannel(), popChannelMap.get(u.getPopChannel())!=null ? popChannelMap.get(u.getPopChannel())+1 : 1);
				downloadChannelMap.put(u.getDownloadChannel(), downloadChannelMap.get(u.getDownloadChannel())!=null ? downloadChannelMap.get(u.getDownloadChannel())+1 : 1);		
			}

			// 统计交易数据 
			SqlPara sqlPara = new SqlPara();
			String sql = "select u.*,t.day_limit,t.create_time as ct,t.bid_name,t.amount,t.target_time,t.status from trade t left join user u on t.user_id=u.id where u.create_time>=? and u.create_time<=? ";
			String yearMonth = DateUtil.getNowYear()+"-"+DateUtil.getNowMonth();
			sqlPara.addPara(Str.nb(params.get("createTimeStart")) ? params.get("createTimeStart") : yearMonth+"-01 00:00:00");
			sqlPara.addPara(Str.nb(params.get("createTimeEnd")) ? params.get("createTimeEnd") : yearMonth+"-31 23:59:59");
			if(Str.nb(getPara("downloadChannel"))) {
				sqlPara.addPara(getPara("downloadChannel"));
				sql += " and download_channel=? ";
			}
			if(Str.nb(getPara("popChannel"))) {
				sqlPara.addPara(getPara("popChannel"));
				sql += " and pop_channel=? ";
			}
			sqlPara.setSql(sql);
			List<Trade> list2 = Trade.me.find(sqlPara);
			for(Trade t : list2) { 
				tradeNum.add(t.getUserId());
				Integer dayLimit = t.getDayLimit();
				tradelMap.put(dayLimit, tradelMap.get(dayLimit)!=null ? tradelMap.get(dayLimit)+1 : 1);
				tradeTotal += t.getAmount();
			}

			// 交易分页数据
			Page<Trade> tradeList = Trade.me.paginate(getParaToInt("page",1), getParaToInt("size",10),sqlPara);
			
			renderJson(Json.ok("数据获取成功！", 
				Data.put("regNum",regNum,"realNameNum",realNameNum,"tradeNum",tradeNum.size(),"tradeTotal",tradeTotal,
					"popChannelMap",popChannelMap,"downloadChannelMap",downloadChannelMap,"tradelMap",tradelMap,"tradeList",tradeList)));
		
		}else if(Str.eq("download", mode)) { //下载渠道统计（Excel导出）
			
			// 初始化参数
			SqlPara sqlPara = new SqlPara();
			String sql = "select u.*,u.create_time as uct,t.create_time as tct,t.bid_name,t.amount,t.target_time,t.status from trade t left join user u on t.user_id=u.id where u.create_time>=? and u.create_time<=? ";
			String yearMonth = DateUtil.getNowYear()+"-"+DateUtil.getNowMonth();
			sqlPara.addPara(Str.nb(getPara("createTimeStart")) ? getPara("createTimeStart") : yearMonth+"-01 00:00:00");
			sqlPara.addPara(Str.nb(getPara("createTimeEnd")) ? getPara("createTimeEnd") : yearMonth+"-31 23:59:59");
			if(Str.nb(getPara("downloadChannel"))) {
				sqlPara.addPara(getPara("downloadChannel"));
				sql += " and download_channel=? ";
			}
			if(Str.nb(getPara("popChannel"))) {
				sqlPara.addPara(getPara("popChannel"));
				sql += " and pop_channel=? ";
			}
			sqlPara.setSql(sql);
			
			// 统计交易数据	
			List<Trade> tradeList = Trade.me.find(sqlPara);
			
			// 创建HSSFWorkbook对象(excel的文档对象)  
			HSSFWorkbook wb = new HSSFWorkbook();  
			
			// 建立新的sheet对象（excel的表单）  
			HSSFSheet sheet = wb.createSheet("用户渠道报表");  
			
			// 设置第一行标题内容
			HSSFRow row1=sheet.createRow(0);  
			int i = 0;
			for(String title : Arrays.asList(new String[] {"用户名","真实姓名","推广渠道","下载渠道","注册时间","实名时间","投资时间","投资标的","金额","还款时间","状态"})) {
				HSSFCell c1=row1.createCell(i);  
				c1.setCellValue(title);  
				sheet.setColumnWidth(i,6000);
				i++;
			}
			
			// 设置详细内容
			i = 1;
			for(Trade t : tradeList) {
				HSSFRow row=sheet.createRow(i++);				
				row.createCell(0).setCellValue(t.getMobile());   				
				row.createCell(1).setCellValue(t.getRealName()); 	
				row.createCell(2).setCellValue(t.getStr("pop_channel")); 
				row.createCell(3).setCellValue(t.getStr("download_channel")); 
				row.createCell(4).setCellValue(DateUtil.format(t.getDate("uct"),DateUtil.DATETIME_DEFAULT_FORMAT));
				row.createCell(5).setCellValue(DateUtil.format(t.getDate("real_name_time"),DateUtil.DATETIME_DEFAULT_FORMAT)); 
				row.createCell(6).setCellValue(DateUtil.format(t.getDate("tct"),DateUtil.DATETIME_DEFAULT_FORMAT)); 
				row.createCell(7).setCellValue(t.getBidName()); 
				row.createCell(8).setCellValue(t.getAmount()+"元"); 
				row.createCell(9).setCellValue(DateUtil.format(t.getTargetTime(),DateUtil.DATETIME_DEFAULT_FORMAT));
				String ss = "投资中";
				if(Str.eq(BidStatus.FULL, t.getStr("status1"))) {
					ss = "已满标";
				}else if(Str.eq(BidStatus.REPAYING, t.getStr("status1"))) {
					ss = "还款中";
				}else if(Str.eq(BidStatus.FINISH, t.getStr("status1"))) {
					ss = "已还款";
				}
				row.createCell(10).setCellValue(ss); 
			}
			
		    // 输出Excel文件  
			HttpServletResponse response = getResponse();
		    OutputStream output = response.getOutputStream();  
		    response.reset();  
		    response.setHeader("Content-disposition", "attachment; filename="+DateUtil.getTimeStamp()+".xls");  
		    response.setContentType("application/msexcel");          
		    wb.write(output);  
		    output.close();  
		    renderNull();
		}
	}
}
 